﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;

/// <summary>
/// Descripción breve de TarjetaDeCreditoDA
/// </summary>
public class TarjetaDeCreditoDA
{
	
		public static DataTable RecuperarTarjeta (string AfiliadoDNI)
        {
        MySqlConnection conexion = Conexion.obtConexion();
        DataTable tabla = new DataTable("Tabla");
        string consulta = @"SELECT
        `tcredito`.`tcreditonumero` AS `Numero`, `tcredito`.`tcreditonombre`
        AS `Nombre`, `bancos`.`banconombre` AS `Banco`
        FROM
         `tcredito` natural join `bancos`
        WHERE
        `tcredito`.`afiliadodni` =" + AfiliadoDNI;
        MySqlCommand comando = new MySqlCommand(consulta, conexion);
        conexion.Open();
        MySqlDataReader dataReader = comando.ExecuteReader();
        tabla.Load(dataReader, LoadOption.OverwriteChanges);
        conexion.Close();
        return tabla;
        }
        
        public static string NuevaTarjeta(string TarjetaNro,string TarjetaNombre, string BancoID, string AfiliadoDNI)
        {
            MySqlConnection conexion = Conexion.obtConexion();
            DataTable tabla = new DataTable("Tabla");
            string consulta = @"Insert into tcredito ( `tcredito`.`tcreditonumero`, `tcredito`.`tcreditonombre`,
            `tcredito`.`bancoid`, `tcredito`.`afiliadodni`) values (" + TarjetaNro + ",'" + TarjetaNombre + "'," + BancoID + "," + AfiliadoDNI + ")";
            MySqlCommand comando = new MySqlCommand(consulta, conexion);
            try
            {
                conexion.Open();
                comando.ExecuteReader();
                conexion.Close();
                return "ok";
            }
            catch
            {
                return consulta;
            }
        }

    public static string EliminarTarjeta(string TarjetaNro)
    {
        MySqlConnection conexion = Conexion.obtConexion();
        DataTable tabla = new DataTable("Tabla");
        string consulta = @"delete from tcredito where  `tcredito`.`tcreditonumero`="+TarjetaNro;
        MySqlCommand comando = new MySqlCommand(consulta, conexion);
        try
        {
            conexion.Open();
            comando.ExecuteReader();
            conexion.Close();
            return "ok";
        }
        catch
        {
            return consulta;
        }

    }
    public static DataTable ListarBancos()
    {
        MySqlConnection conexion = Conexion.obtConexion();
        DataTable tabla = new DataTable("Tabla");
        string consulta = @"SELECT
        `bancos`.`bancoid` AS `ID`, `bancos`.`banconombre` AS `Nombre`
        FROM `bancos`";
        MySqlCommand comando = new MySqlCommand(consulta, conexion);
        conexion.Open();
        MySqlDataReader dataReader = comando.ExecuteReader();
        tabla.Load(dataReader, LoadOption.OverwriteChanges);
        conexion.Close();
        return tabla;
    }
    public static string ModificarTarjeta(string TarjetaNro, string TarjetaNom, string TarjetaBanco, string AfiliadoDni)
    {
        MySqlConnection conexion = Conexion.obtConexion();
        DataTable tabla = new DataTable("Tabla");
        string consulta = @"update tcredito set `tcredito`.`tcreditonumero`="+TarjetaNro+", `tcredito`.`tcreditonombre`='"+TarjetaNom+
            "', `tcredito`.`bancoid`=" + TarjetaBanco + " where  `tcredito`.`afiliadodni`=" + AfiliadoDni;
        MySqlCommand comando = new MySqlCommand(consulta, conexion);
        try
        {
            conexion.Open();
            comando.ExecuteReader();
            conexion.Close();
            return "ok";
        }
        catch
        {
            return consulta;
        }

    }
    public static DataTable ListarFormasdePago()
    {
        MySqlConnection conexion = Conexion.obtConexion();
        DataTable tabla = new DataTable("Tabla");
        string consulta = @"SELECT
         `formaspago`.`formapagodesc` AS `Descripcion`, `formaspago`.`formapagoid` AS `ID`
        FROM `formaspago`";
        MySqlCommand comando = new MySqlCommand(consulta, conexion);
        conexion.Open();
        MySqlDataReader dataReader = comando.ExecuteReader();
        tabla.Load(dataReader, LoadOption.OverwriteChanges);
        conexion.Close();
        return tabla;
    }
}
